<?php
// P. Couchet 25/05/2011
class model {
	function model($cnxOpt, $user, $pass){
		try {
			$this->cnxDB = new PDO($cnxOpt, $user, $pass, array(PDO::MYSQL_ATTR_USE_BUFFERED_QUERY => true));
			$this->cnxDB->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
			$this->cnxDB->query("SET NAMES 'utf8'");
			}
		catch (PDOException $dbex) {die("Erreur de connexion : " . $dbex->getMessage() );}
	}
	// dans status.php
	function status($bdd, $data){
		try {
			// Total
			$req = "SELECT COUNT(*) FROM `$data`";
			$prep = $this->cnxDB->prepare($req);
			$prep->execute();
			$itemNumber = $prep->fetch();
			// Numérisés
			$req = "SELECT COUNT(*) FROM `$data` WHERE `url_md` != ''";
			$prep = $this->cnxDB->prepare($req);
			$prep->execute();
			$digitItemNumber = $prep->fetch();
			// Nombre de pages
			$req = "SELECT SUM(pagination) FROM `$data` WHERE `url_md` != ''";
			$prep = $this->cnxDB->prepare($req);
			$prep->execute();
			$digitPageNumber = $prep->fetch();
		} catch (Exception $e) {
	    echo 'Erreur dans une des requêtes de status<br/>';
	    echo $e->getMessage().'<br/>';
	    echo $req;
		}
		return array('itemNumber'=>$itemNumber[0], 'digitItemNumber'=>$digitItemNumber[0], 'digitPageNumber'=>$digitPageNumber[0]);
	}
	// Traitement d'un Item, depuis bibtex.php ou depuis l'admin
	function selectItem($data, $idKey, $id){
		try {
			$req = "SELECT * FROM `$data` WHERE `$idKey` = '$id'";
			$res = $this->cnxDB->query($req);
			$result = $res->fetchAll();
		} catch (Exception $e) {
	    echo 'Erreur dans la requête : '.$req.'<br/>';
	    echo $e->getMessage().'<br/>';
	    echo $req;
		}
		return $result;
	}
	function updateItem($dbname, $from, $fieldTable, $id, $post){
		try {
			// building of the query
			$query = "UPDATE `$dbname`.`$from` SET ";
			foreach($fieldTable as $key=>$value){
				$query .= "`$key` = '".addslashes($post[$key])."', ";
			}
			$query = preg_replace("/, $/", ' ', $query);
 			$query .= "WHERE `$from`.`id` =".$id.";";
			// execute
			$res = $this->cnxDB->prepare($query);
			$result = $res->execute();
		} catch (Exception $e) {
	    echo 'Erreur dans la requête : '.$query.'<br/>';
	    echo $e->getMessage().'<br/>';
		}
		if (isset($result)) return $result;
	}
	function deleteItem($data, $idKey, $id){
	}
	// appelé de aAction.php, sAction.php, tAction.php
	function whereLikeValue($selectArray, $whereLikeArray, $whereLikeBool){
		// Fabrication de select
		$selectValue = '';
		foreach($selectArray as $key=>$value) $selectValue .= "`$key` , ";
		$selectValue = rtrim($selectValue, " , ");
		// Fabrication de whereLikeValue (range de date possible)
		$whereLikeValue = '(';
		foreach($whereLikeArray as $key=>$value){
			$whereLikeValue .= "(`$key` LIKE '".addslashes($value)."'";
			if ($key=='yr' and (!empty($_GET['yrEnd']) and ($_GET['yrEnd']>=$_GET['yr']))){
				$i=$_GET['yr'];
				while($i<=$_GET['yrEnd']){
					$whereLikeValue .= " OR (`$key` LIKE '$i')";
					$i++;
				}
			}
			$whereLikeValue .= ") $whereLikeBool ";
		}
		$whereLikeValue = rtrim($whereLikeValue, " $whereLikeBool ");
		$whereLikeValue .= ')';
		// Résultats
		return $whereLikeValue;
	}
	// appelé de aAction.php, sAction.php, tAction.php
	function itemNumber($from, $whereLikeValue){
		try {
			// Nombre de notices
			$req = "SELECT COUNT(*) FROM `$from` WHERE $whereLikeValue ";
			$prep = $this->cnxDB->prepare($req);
			$prep->execute();
			$itemNumber = $prep->fetchColumn();
		} catch (Exception $e) {
	    echo 'Erreur dans la requête : '.$req.'<br/>';
	    echo $e->getMessage().'<br/>';
	    echo $req;
		}
		// Résultats
		return $itemNumber;
	}
	// appelé de aAction.php, sAction.php, tAction.php
	function items($from, $whereLikeValue, $orderBy, $limitBegin, $limitLength){
		try {
			$req = "SELECT * FROM `$from` WHERE $whereLikeValue ";
			if(isset($orderBy))$req .= "ORDER BY $orderBy ";
			$req .= "LIMIT $limitBegin, $limitLength";
			$prep = $this->cnxDB->prepare($req);
			$prep->execute();
			$items = $prep->fetchAll();
		} catch (Exception $e) {
	    echo 'Erreur dans la requête : '.$req.'<br/>';
	    echo $e->getMessage().'<br/>';
	    echo $req;
		}
		// Résultats
		return $items;
	}
	// $select est un champ
	function selectDistinct($select, $from){
		try {
			$req = "SELECT DISTINCT $select FROM `$from` ORDER BY `$select`";
			$res = $this->cnxDB->query($req);
			$result = $res->fetchAll();
		} catch (Exception $e) {
	    echo 'Erreur dans la requête : '.$req.'<br/>';
	    echo $e->getMessage().'<br/>';
	    echo $req;
		}
		return $result;
	}
	// Statistiques sur la nationalité
	function statisticsNat(){
		try {
			$req = "SELECT nat, count(*) as number FROM `data` GROUP BY nat order by number desc,nat";
			$res = $this->cnxDB->query($req);
			$result = $res->fetchAll();
		} catch (Exception $e) {
	    echo 'Erreur dans la requête : '.$req.'<br/>';
	    echo $e->getMessage().'<br/>';
	    echo $req;
		}
		return $result;
	}
	// Statistiques sur le nom
	function statisticsLnFnRedacteur(){
		try {
			$req = "SELECT lnfn, COUNT(*) AS number, yr FROM `data` WHERE `job`='Redacteur' GROUP BY lnfn ORDER BY yr";
			$res = $this->cnxDB->query($req);
			$result = $res->fetchAll();
		} catch (Exception $e) {
	    echo 'Erreur dans la requête : '.$req.'<br/>';
	    echo $e->getMessage().'<br/>';
	    echo $req;
		}
		return $result;
	}
	function statisticsLnFnMax(){
		try {
			$req = "SELECT lnfn, COUNT(*) AS number FROM `data` GROUP BY lnfn ORDER BY number DESC LIMIT 0,20";
			$res = $this->cnxDB->query($req);
			$result = $res->fetchAll();
		} catch (Exception $e) {
	    echo 'Erreur dans la requête : '.$req.'<br/>';
	    echo $e->getMessage().'<br/>';
	    echo $req;
		}
		return $result;
	}
	function statisticsDate(){
		try {
			$req = "SELECT yr, COUNT(*) AS number FROM `data` GROUP BY yr ORDER BY yr";
			$res = $this->cnxDB->query($req);
			$result = $res->fetchAll();
		} catch (Exception $e) {
	    echo 'Erreur dans la requête : '.$req.'<br/>';
	    echo $e->getMessage().'<br/>';
	    echo $req;
		}
		return $result;
	}

}

?>